﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using InstrumentCheck.Module;
using System.Data;
using System.Data.SqlClient;

namespace InstrumentCheck.DAL
{
    public class MailJob_dal
    {
        public static List<AlertMail> getAlertList()
        {
            List<AlertMail> alertmail = new List<AlertMail>();
            string sql = " select correct_period, fas_type,fas_no,correct_date,'N' AS USER_Response from correct_item";
            sql += " where status = 1 and getdate()>=DateAdd ";
            sql += " (Mm,case correct_period when 1 then 6 when 2 then 12 when 3 then 24 end,correct_date) -28";
            DataSet ds = new DataSet();
            SqlDataAdapter ada = new SqlDataAdapter();
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                    addAlertList(row, alertmail);
            }
            return alertmail;

        }
        public static void addAlertList(DataRow row, List<AlertMail> alertmail)
        {
            AlertMail mail = new AlertMail();
            switch(row["User_Response"].ToString())
            {
                case "N":
                    mail.HasResponse = false;
                    break ;
                case "Y" :
                    mail.HasResponse =true;
                    break;
                default :
                    break;
            }
            string correctdate = row["correct_date"].ToString();
            Item item = new Item();
            item.CorrectCycle = (CorrectCycle)row["correct_period"];
            switch (item.CorrectCycle)
            { 
                case CorrectCycle.HalfYear :
                    item.NeedCorrectDate = DateTime.Parse(correctdate).AddMonths(6);
                    break;
                case CorrectCycle.OneYear :
                    item.NeedCorrectDate = DateTime.Parse(correctdate).AddMonths(12);
                    break;
                case CorrectCycle .TwoYears :
                    item.NeedCorrectDate = DateTime.Parse(correctdate).AddMonths(24);
                    break;
                default :
                    break;

            }
            
            item.AssetType = (AssetType)row["fas_type"];
            item.AssetNO = row["fas_no"].ToString();
            mail.Item = item;
            alertmail.Add(mail);
            
        }
        public static void InsertAlertList(AlertMail alertMail)
        {
            string sql = " insert into Alert_list values(@fastype,@fasno,@correctdate,@userresponse)";
            SqlParameter[] para = new SqlParameter[4];
            para[0] = SQLHelper.SQLHelper.CreateParameter("fastype", SqlDbType.Int, alertMail.Item.AssetType, ParameterDirection.Input);
            para[1] = SQLHelper.SQLHelper.CreateParameter("fasno", SqlDbType.NVarChar, alertMail.Item.AssetNO, ParameterDirection.Input);
            para[2] = SQLHelper.SQLHelper.CreateParameter("correctdate", SqlDbType.DateTime, alertMail.Item.NeedCorrectDate, ParameterDirection.Input);
            string userresponse = alertMail.HasResponse ? "Y":"N";
            para[3] = SQLHelper.SQLHelper.CreateParameter("userresponse", SqlDbType.NVarChar, userresponse, ParameterDirection.Input);
            SQLHelper.SQLHelper.ExecuteNonQuery(CommandType.Text, sql, para);
            
        }
        public static bool  checkAlertList(string fasno,DateTime correctdate,CorrectCycle period)
        {
            bool IsExist = false;
            string sql = "select * from Alert_List where fas_no=@fasno and correct_date = @correctdate";
            SqlParameter[] para = new SqlParameter[2];
            para[0] = SQLHelper.SQLHelper.CreateParameter("fasno",SqlDbType.NVarChar ,fasno,ParameterDirection.Input);
            para[1] = SQLHelper.SQLHelper.CreateParameter("correctdate", SqlDbType.DateTime, correctdate, ParameterDirection.Input);
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, para);
            if (ds.Tables[0].Rows.Count > 0)
                IsExist = true;
            return IsExist;

        }
        public static void UpdateAlertList(string fasno, DateTime correctdate)
        {
            string sql = "";
        }
        public static List<AlertMail>  MailInfo()
        {
            List<AlertMail> alertmail = new List<AlertMail>();
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            string sql = "sp_MailInfo";
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.StoredProcedure, null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                {
                    alertmail.Add(addMailInfo(row));
                }
            }
            return alertmail;

        }
        public static AlertMail ApprovalMailInfo(Item item)
        {
            AlertMail alertmail = new AlertMail();
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            string sql = "sp_ApprovalMailInfo";
            SqlParameter[] par = new SqlParameter[2];
            par[0] = SQLHelper.SQLHelper.CreateParameter("fasno", SqlDbType.NVarChar, item.AssetNO, ParameterDirection.Input);
            par[1] = SQLHelper.SQLHelper.CreateParameter("correctdate", SqlDbType.DateTime, item.NeedCorrectDate, ParameterDirection.Input);
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.StoredProcedure, par);
            if (ds.Tables[0].Rows.Count > 0)
                alertmail= addMailInfo(ds.Tables[0].Rows[0]);
            return alertmail;
        }
 
        public static AlertMail addMailInfo(DataRow row)
        {
            AlertMail mail = new AlertMail();
            Item item = new Item();
            item.NeedCorrectDate = DateTime.Parse(row["correct_date"].ToString());
            item.AssetNO = row["fas_no"].ToString();
            item.ChiName = row["chi_name"].ToString();
            item.Spec = row["spec"].ToString();
            item.AssetType = (AssetType)row["fas_type"];
            User user = new User();
            user.EmpNO = row["EmpNO"].ToString();
            user.EmpEngName = row["EName"].ToString();
            user.EmpChineseName = row["CName"].ToString();
            user.Email = row["MailAddress"].ToString();
            item.Keeper = user;
            mail.Item = item;
            return mail;

        }
   
        public static List<User> MailCC()
        {
            List<User> user = new List<User>();
            SqlDataAdapter ada = new SqlDataAdapter();
            DataSet ds = new DataSet();
            string sql = " select a.resav002 as EmpNO ,b.resak005 as MailAddress ";
            sql += " from resav a inner join resak b on a.resav002=b.resak001";
            SQLHelper.SQLHelper.Fill(ada, ds, sql, CommandType.Text, null);
            if (ds.Tables[0].Rows.Count > 0)
            {
                foreach (DataRow row in ds.Tables[0].Rows)
                    addMailCC(row, user);
            }
            return user;
        }
        public static void addMailCC(DataRow row,List<User> user)
        {
            User tmpUser = new User();
            tmpUser.EmpNO = row["EmpNO"].ToString();
            tmpUser.Email = row["MailAddress"].ToString();
            user.Add(tmpUser);
        }
    }
}
